﻿using System;
using System.Collections.Generic;
using System.Web;
using hdbankwebservice.DTO;
using System.Data.SqlClient;
using System.Data;

namespace hdbankwebservice.DAO
{
    public class ThongTinCaNhanDAO
    {
        public static ThongTinCaNhan LayThongTinTaiKhoanQuaSoThe(string soThe)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"select kh.HOTEN, t.SOTHE, tk.SOTAIKHOAN, tk.SOTIENGOC, tk.SOTINDUNGCONLAI, tk.SOTINDUNGTOIDA, ts.TENDIENGIAI
                                from THETINDUNG t, TAIKHOANKHACHHANG tk, KHACHHANG kh, THAMSOTINDUNG ts
                                where t.MAKHACHHANG = kh.MAKHACHHANG and kh.SOTAIKHOAN = tk.SOTAIKHOAN  and ts.MATHAMSOTINDUNG = tk.MATHAMSOTINDUNG
		                                and t.SOTHE = '" + MineUtils.ByteArrayToString(MineUtils.MaHoa(soThe)) + "'";
            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                ThongTinCaNhan user = new ThongTinCaNhan();
                user.TenSoHuu = dt.Rows[0]["HOTEN"].ToString();
                user.SoThe = MineUtils.GiaiMa(MineUtils.StringToByteArray(dt.Rows[0]["SOTHE"].ToString()));
                user.SoTaiKhoan = dt.Rows[0]["SOTAIKHOAN"].ToString();
                user.SoTienGoc = (int)dt.Rows[0]["SOTIENGOC"];
                user.SoTinDungConLai = (int)dt.Rows[0]["SOTINDUNGCONLAI"];
                user.SoTinDungToiDa = (int)dt.Rows[0]["SOTINDUNGTOIDA"];
                user.LoaiTinDung = dt.Rows[0]["TENDIENGIAI"].ToString();
                return user;
            }
        }

        public static DataTable LayThongTinTaiKhoan(string soThe)
        {
            SQLDataAccessHelper dl = new SQLDataAccessHelper();
            string sqlString = @"select kh.HOTEN, t.SOTHE, tk.SOTAIKHOAN, tk.SOTIENGOC, tk.SOTINDUNGCONLAI, tk.SOTINDUNGTOIDA, ts.TENDIENGIAI
                                from THETINDUNG t, TAIKHOANKHACHHANG tk, KHACHHANG kh, THAMSOTINDUNG ts
                                where t.MAKHACHHANG = kh.MAKHACHHANG and kh.SOTAIKHOAN = tk.SOTAIKHOAN  and ts.MATHAMSOTINDUNG = tk.MATHAMSOTINDUNG
		                                and t.SOTHE = '" + MineUtils.ByteArrayToString(MineUtils.MaHoa(soThe)) + "'";
            SqlCommand sql = new SqlCommand(sqlString);
            DataTable dt = dl.ThucThiTruyVan(sql);
            if (dt.Rows.Count == 0)
            {
                return null;
            }
            return dt;
        }
    }
}